EXCEL ADVANCE TRAINING

Welcome

Introduction

For this training will be looking at spreadsheets using the Microsoft Excel.

Numerous spread sheet application/software are available just to name a few:

  • Microsoft Excel
  • LibreOffice
  • Google sheets

Some of the spreadsheets are paid for others a free and open source. Access can be both online (using your browers i.e. Internet Explorer/Google Chrome/Mozilla) and offline (As a stand alone software installed on your computer/mobile phone)

Assumptions

Have some limited or little knowledge of Microsoft excel functions e.g =sum(C1,C2), though not mandatory it makes it easier to grasp concepts slightly faster.

Though if you remember the simple mathematics which we do everyday of summing and adding up money then you are good :).

Requirements

  • Working computer (Laptop/Desktop)
  • Access to internet once in a while to download a few resource materials
  • Interest and desire to learn new concepts

Overview of our knowledge

  • Operating and maneuvering on a PC/Laptop
  • Basic knowledge of Ms Excel Interface

Teaching guide

  • Mixed instructional guide, with the presentation but focused on getting more hard skills
  • Prepare to write Excel formulas
  • Engagements and practice on the fly

About the trainer

Just me but you can see you can get the details from qprop

Recap

Formula

Different option to input formula:

Using the menu : Menu Formulas then pick the options menus available

Direct formula in cell : With the cell start with = followed with the formula. You have to have an understanding of the formula

My cardinal rule in excel there is always more than one way to achieve the same result.

Lets look at example:

  How can we sum up different numbers in different cells to get the total

Day 4

Formula Auditing

Having formulas is great, knowing where they are and how they interact with each other.

To help us with this is where auditing tools come in.

From Formula > (FOrmula Auditing), you can see the different auditing functions.

  • Show Formulas
  • Using shortcuts
  • Trace Precedents
  • Trace Dependents
  • Evaluate Formula

Get our hands dirty

Download data downloand click on google drive link here this will send a request to access and once accepted you can access the data files here.

Using the _02_PlayData.xlsx attempt the below practices

  • LocateFormulas sheet locate the formulas and highlight them
  • AuditingTools sheet Trace the formula with arrows where they are coming from and heading on cell D13, B3

Date Functions

Introduction

Date is one of the most complicated types of data to deal with, there are different ways of presenting date data types.

Day-Month-Year - 17-09-2023

Month-Day-Year - 09-17-2023

Month-Day-Year - Sep-17-2023

Month-Day-Year - Sep-17-23

Year-Month-Day - 2023-09-17

Year-Day-Month - 2023-17-09

Date Formulas

Under the Formulas > DATE:

  • TODAY
  • MONTH
  • DAY
  • YEAR
  • NOW
  • DATEVALUE
  • TIMEVALUE

Get our hands dirty

We will use the _01_Sample_data.xlsx data which can be gotten in the Data\_01_Sample_data.xlsx folder.

Using the data sample attempt the below practices

  • From ‘Order Date’ pick the Year of Order
  • From ‘Order Date’ pick the Month of Order
  • From ‘Order Date’ pick the Day of Order
  • From ‘Order Date’ pick the Weekday of Order .i.e “Monday, Tuesday, Wednesday e.t.c”
  • Determine the difference in days between Order Date and Ship Date

Day 5

Data Tools (Data Validation)

Data validations are steps that are used to manage, process and clean your data.

They help manage the data and align your data to special limits that you would want

From Data > (Data Tools), you can seee the different data tools.

  • Data Validation
    • Any Value
    • Whole Number
    • Decimal
    • List
    • Text length
  • Text to Columns
  • Remove Duplicates

Get our hands dirty

Using the sample attempt the below practices

  • Under the “Orders” sheet using “Order ID” create a new sheet with unique Order ID

  • Seperatethe ‘Order ID’ column to three parts ‘State Code’, ‘Year Order’, ‘Serial Order Number’

  • Unite back into one cell using a ‘:’ as the separate of the three new columns created above using a formula the new column name ‘New Order ID’

  • Create a data entry sheet called ‘Data Entry’ with data validation that accepts the below values - Name (Any Type of value) - Age (Whole Number) - Weight (Decimal) - Date of Signing in (Date) - Mobile Number (Limiting the text length to 12 number digits)

Macros | Recording Steps

At times we need to be able to automate some repetitive tasks, if you do it on a daily or repeat exact same steps severally you can record macros which enable you to re-use them.

Easiest way is by using macros.

To do that you need to enable the Developer menu bar. Go to Options > Customize Ribbon > Developer (Tick) then click ok. This adds the developer tab on your menu.

From Developer, you can see the different developer tools.

  • Use Relative Reference
  • Record Macro
  • Stop Macro

Get our hands dirty

Using the sample attempt the below practices _01_Sample_data.xlsx

  • Record a basic macro that deletes a column, using a new shortcut key you have developed Ctrl+Shift+w
  • Record a basic macro that deletes a row, using a new shortcut key you have developed Ctrl+Shift+T
  • Record a basic macro that shades a whole column ’orange`
  • Record a basic macro that adds a new sheet

Visualization




Data can be fun | Visualization and story telling







Beauty lies in the eyes of the beholder, data has to be simplified further to make sense to be able to achieve this we need to have visuals e.g graphs or charts.

Get our hands dirty

Download EPL (English Premier League data) downloand click on google drive link here this will send a request to access and once accepted you can access the data files here.

Second option here and original sources Requires Internet

Data content: - 10 separate csv files - Each csv is all the games play from 2009 to 2019 - All the columns are exactly the same

Exercise: - Import all the files into 1 excel work book (Use any method you deem fit)

Bonus Materials

Other Concepts

  • Locating formulas
  • Auditing Tools
  • Column/Row References A:A / 1:1

Shortcut CheatSheet

ITEM Short Cut Description
BASIC CTRL+C Copy
CTRL+V Paste
CTRL+F Find/Search
CTRL+H Find & Replace
CTRL+G Go To Special Dialogue
CTRL+ALT+V Paste Special
CTRL+; Current Date
CTRL+SHIFT+; Current Time
CTRL+SPACE Select Column
SHIFT+SPACE Select Row
WORKBOOK
CTRL+N Open New Workbook
F12 Save as Dialoge box
F2 Enter cell to edit
F4 Repeat previous keyboard action
FORMATTNG
CTRL+1 Formating cell dialogue
CTRL+2 Cell Bold
CTRL+3 Cell Italics
CTRL+SHIFT+1 Formating cell to Number
CTRL+SHIFT+2 Formating cell to Custom
CTRL+SHIFT+5 Formating cell to %
CTRL+SHIFT+1 Formating cell to Number
INSERTION/DELETION
CTRL+SHIFT++ Insert Dialoge box
CTRL+- Delete Dialoge box
CTRL+( Hide row
CTRL+SHIFT+( Unhide row
CTRL+) Hide column
SHEET MANIPULATION
CTRL+A Select all data
CTRL+. Move to the corners of each selected array